USE [NANS21V]
GO
/****** オブジェクト:  StoredProcedure [dbo].[ResultJumpOfHeight_smh]    スクリプト日付: 01/26/2026 16:19:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ResultJumpOfHeight_smh]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ResultJumpOfHeight_smh]
GO

CREATE PROCEDURE [dbo].[ResultJumpOfHeight_smh]
	@TAIKAINO int,
	@KYOGINO int,
	@ROUNDNO int,
	@SUBNO int,
	@KUMINO int,
	@LANE int,
	@SYUMOKUKUBUN int,
	@EXE2FLG int,
	@SIGIFINDFIG int
AS
DECLARE @SQL AS VARCHAR(MAX)

	SET @SQL = '
		SELECT * FROM(
            SELECT DISTINCT 
                RS.LANE,
                RS.SIGINO,
                RS.SIGIVAL,
                (KS.NUMBER1 + CONVERT(varchar, KS.NUMBER2)) AS NUMBER, 
                (CONVERT(varchar, KS.KYOGISYAKANA) + ''<br/>'' + CONVERT(varchar, KS.KYOGISYAMEI)) AS KYOGISYAMEI, 
                (CONVERT(varchar, S.SYOZOKUMEI) + ''<br/>'' + CONVERT(varchar, KS.KOJINSYOZOKUTIMEI)) AS SYOZOKUMEI,
                (dbo.JumpOfHeight(REPLACE(CONVERT(varchar, RS.SIGIKIROKU),'','',''''))) AS EXTEND1,
                CASE WHEN RE.KIROKU = 0 OR RE.KIROKU IS NULL THEN '''' ELSE dbo.Kiroku(RE.KIROKU, '+ STR(@SYUMOKUKUBUN) +', 3, 3)END AS KIROKU,
                dbo.Jyuni((CASE WHEN STR(RE.JYUNI) = 0 OR RE.KIROKU IS NULL THEN '''' ELSE STR(RE.JYUNI) END) ,SK.OPENFLG) AS JYUNI, 
                (dbo.CommentCodeConvert(RE.RCOMMENT,LA.SYUKKETU)) AS RCOMMENT,
                RS.EXE2FLG,
                RE.EXTEND3, 
                LA.KYOGISYANO,
                KS.SEINEN,
                KS.GAKUNEN,
                RE.EXTEND5,
                SK.KIROKU AS PREKIROKU,
                KSE.EXTEND8 AS KSE_EXTEND8,
                KONSEI.KIROKU AS KPREKIROKU 
            FROM TBL_KUMI AS TKU, 
                TBL_LANE AS LA
                FULL OUTER JOIN 
                (
                    SELECT DISTINCT 
                        RE.LANE,
                        RE.KUMINO,
                        RE.KIROKU,
                        RE.JYUNI,
                        RE.RCOMMENT,
                        RE.EXTEND3, 
                        RE.EXTEND1,
                        RE.EXTEND5 
                    FROM TBL_LANE AS LA, 
                        TBL_RESULT AS RE
                    WHERE LA.TAIKAINO = '+ STR(@TAIKAINO) +' 
                        AND LA.KYOGINO = '+ STR(@KYOGINO) +'
                        AND LA.ROUNDNO = '+ STR(@ROUNDNO) +'
                        AND LA.SUBNO = '+ STR(@SUBNO) +'
                        AND LA.KUMINO = '+ STR(@KUMINO) +'
                        AND LA.LANE = '+ STR(@LANE) +'
                        AND LA.TAIKAINO = RE.TAIKAINO 
                        AND LA.KYOGINO = RE.KYOGINO 
                        AND LA.SUBNO = RE.SUBNO 
                        AND LA.ROUNDNO = RE.ROUNDNO 
                        AND LA.KUMINO = RE.KUMINO 
                )AS RE ON LA.LANE = RE.LANE AND LA.KUMINO = RE.KUMINO 
                LEFT OUTER JOIN 
                (
                    SELECT DISTINCT 
                        RS.LANE, 
                        RS.KUMINO, 
                        RS.SIGINO,
                        RE.KIROKU,
                        RS.KIROKU AS SIGIVAL,
                        RS.EXTEND1 AS SIGIKIROKU,
                        RS.EXTEND2 AS EXE2FLG
                    FROM TBL_LANE AS LA, 
                            TBL_RESULT_SIGI AS RS,
                            TBL_RESULT AS RE
                    WHERE LA.TAIKAINO = '+ STR(@TAIKAINO) +' 
                        AND LA.KYOGINO = '+ STR(@KYOGINO) +'
                        AND LA.ROUNDNO = '+ STR(@ROUNDNO) +'
                        AND LA.SUBNO = '+ STR(@SUBNO) +'
                        AND LA.KUMINO = '+ STR(@KUMINO) +'
                        AND LA.LANE = '+ STR(@LANE) +'
                        AND LA.TAIKAINO = RE.TAIKAINO 
                        AND LA.KYOGINO = RE.KYOGINO 
                        AND LA.SUBNO = RE.SUBNO 
                        AND LA.ROUNDNO = RE.ROUNDNO 
                        AND LA.KUMINO = RE.KUMINO 
                        AND LA.LANE = RE.LANE 
                        AND LA.TAIKAINO = RS.TAIKAINO 
                        AND LA.KYOGINO = RS.KYOGINO 
                        AND LA.ROUNDNO = RS.ROUNDNO 
                        AND LA.SUBNO = RS.SUBNO 
                        AND LA.KUMINO = RS.KUMINO
                        AND LA.LANE = RS.LANE 
        '

		IF @SIGIFINDFIG = 1
			BEGIN
				SET @SQL = @SQL + ' AND RS.EXTEND2 LIKE CASE WHEN '+ STR(@EXE2FLG) +' = 0 THEN '''' ELSE CONVERT(varchar ,'+ STR(@EXE2FLG) +') END'
			END
	SET @SQL = @SQL + '
                )AS RS ON LA.LANE = RS.LANE AND LA.KUMINO = RE.KUMINO
                left join TBL_KYOGISYA AS KS on LA.TAIKAINO = KS.TAIKAINO AND LA.KYOGISYANO = KS.KYOGISYANO 
                left join TBL_KYOGISYA_EXTE KSE on KS.TAIKAINO = KSE.TAIKAINO and KS.KYOGISYANO = KSE.KYOGISYANO
                LEFT JOIN TBL_KYOGI AS KYOGI on LA.TAIKAINO = KYOGI.TAIKAINO AND LA.KYOGINO = KYOGI.KYOGINO 
                LEFT JOIN TBL_KONSEI_MASTER AS KM
                    ON KM.SYUMOKUNO = KYOGI.SYUMOKUNO   
                    AND KM.KONSEINO = '+ STR(@SUBNO) +'
                LEFT JOIN TBL_KYOGISYA_KONSEI_UCHIWAKE_KIROKU as KONSEI 
                    ON LA.TAIKAINO = KONSEI.TAIKAINO AND LA.KYOGINO = KONSEI.KYOGINO 
                    AND KONSEI.SYUMOKUNO = KM.KONSEINO AND KONSEI.KYOGISYANO = KS.KYOGISYANO, 
                TBL_SYOZOKU AS S, 
                TBL_SYOZOKUTI AS ST, 
                TBL_SANKAKYOGI AS SK 
            WHERE LA.TAIKAINO = '+ STR(@TAIKAINO) +'
                AND LA.KYOGINO = '+ STR(@KYOGINO) +'
                AND LA.ROUNDNO = '+ STR(@ROUNDNO) +'
                AND LA.SUBNO = '+ STR(@SUBNO) +'
                AND LA.KUMINO = '+ STR(@KUMINO) +'
                AND LA.LANE = '+ STR(@LANE) +'
                AND LA.TAIKAINO = KS.TAIKAINO 
                AND LA.KYOGISYANO = KS.KYOGISYANO 
                AND LA.TAIKAINO = SK.TAIKAINO 
                AND LA.KYOGISYANO = SK.KYOGISYANO 
                AND LA.KYOGINO = SK.KYOGINO 
                AND KS.TAIKAINO = S.TAIKAINO 
                AND KS.SYOZOKUNO1 = S.SYOZOKUNO 
                AND S.SYOZOKUTINO = ST.SYOZOKUTINO 
                AND TKU.TAIKAINO = '+ STR(@TAIKAINO) +'
                AND TKU.KYOGINO = '+ STR(@KYOGINO) +'
                AND TKU.SUBNO = '+ STR(@SUBNO) +'
                AND TKU.ROUNDNO = '+ STR(@ROUNDNO) +'
        ) AS P
        GROUP BY 
            P.LANE,
            P.SIGINO,
            P.SIGIVAL,
            P.NUMBER, 
            P.KYOGISYAMEI, 
            P.SYOZOKUMEI,
            P.EXTEND1,
            P.KIROKU,
            P.JYUNI, 
            P.RCOMMENT,
            P.EXTEND3, 
            P.EXE2FLG,
            P.KYOGISYANO,
            P.SEINEN,
            P.GAKUNEN,
            P.EXTEND5,
            P.PREKIROKU,
            P.KSE_EXTEND8,
            P.KPREKIROKU 
        ORDER BY
		' 

	IF @EXE2FLG = 0
		BEGIN
			SET @SQL = @SQL + 'CASE WHEN P.EXE2FLG = ''1'' THEN 1 ELSE 0 END ASC, P.SIGIVAL'
		END
	ELSE 
		BEGIN
			SET @SQL = @SQL + ' P.SIGINO'
		END
	--PRINT @SQL
	EXECUTE(@SQL)

RETURN 0